use master
go

create database ABC
on
(
	name='ABC',
	filename='D:\ABC.mdf',
	size=5,
	maxsize=50,
	filegrowth=10%
)
log on
(
	name='ABC_log',
	filename='D:\ABC_log.ldf',
	size=5,
	maxsize=50,
	filegrowth=10%
)
go
use ABC
go

create table orders
(
	orderId int primary key identity ,
	orderDate datetime
)
go

create table orderItem
(
	ItemiD int primary key identity ,
	orderId int references orders(orderId),
	itemType varchar(10),
	itemName varchar(10),
	theNumber int ,
	theMoney int
)
go

insert orders values 
('2008-01-12'),('2008-02-10'),
('2008-02-15'),('2008-03-10')
go

insert orderItem values
('1','文具','笔',72,2),
('1','文具','尺',10,1),
('1','体育用品','篮球',1,56),
('2','文具','笔',36,2),
('2','文具','固体胶',20,3),
('2','日常用品','透明胶',2,1),
('2','体育用品','羽毛球',20,3),
('3','文具','订书机',20,3),
('3','文具','订书针',10,3),
('3','文具','裁纸刀',5,5),
('4','文具','笔',20,2),
('4','文具','信纸',50,1),
('4','日常用品','毛巾',4,5),
('4','日常用品','透明胶',30,1),
('4','体育用品','羽毛球',20,3)
go

select SUM(theNumber)as 总数 from dbo.orderItem
go

select SUM(theNumber) as 数量 ,AVG(theMoney) as 平均单价 from dbo.orderItem where orderId<3 group by orderId having AVG(theMoney)<10 
go

select SUM(theNumber) as 数量,AVG(theMoney) as 平均单价 from dbo.orderItem where theNumber<50 group by orderId having AVG(theMoney)<10
go

select '文具：' as 类型,COUNT(itemType) 数量 from orderItem where itemType='文具' union
select '体育用品：' as 类型,COUNT(itemType) 数量 from orderItem where itemType='体育用品' union
select '日常用品：' as 类型, COUNT(itemType) 数量 from orderItem where itemType='日常用品' 
go

select itemType,SUM(theNumber) as 数量,AVG(theMoney) as 平均单价 from dbo.orderItem where theNumber>100 group by itemType 
go

select itemName as 产品名称,COUNT(itemName) as 订购次数,SUM(theNumber) as 总数量,AVG(theMoney) as 平均单价 from dbo.orderItem group by itemName
go
--使用上次作业的订单数据库，完成下列题目：

--1.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价
select * from  orders
select * from orderItem
SELECT ItemiD,orderDate,itemType,itemName,theNumber,theMoney from orderItem 
inner join orders on orderItem.orderId=orders.orderId
--2.查询订购数量大于50的订单的编号，订单日期，订购产品的类别和订购的产品名称
SELECT ItemiD,orderDate,itemType,itemName,theNumber,theMoney from orderItem 
inner join orders on orderItem.orderId=orders.orderId where theNumber>50
--3.查询所有的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价
SELECT ItemiD,orderDate,itemType,itemName,theNumber,theMoney, theNumber*theMoney 总价 from orderItem 
inner join orders on orderItem.orderId=orders.orderId 

--4.查询单价大于等于5并且数量大于等于50的订单的订单的编号，订单日期，订购产品的类别和订购的产品名称，订购数量和订购单价以及订购总价
SELECT ItemiD,orderDate,itemType,itemName,theNumber,theMoney from orderItem 
inner join orders on orderItem.orderId=orders.orderId where theNumber>50 and theMoney>1
--5.查询每个订单分别订购了几个产品，例如：
--			编号   订购产品数
--                                            	  1           3
--                                            	  2           4
select ItemiD,COUNT(orderId) from orderItem group by I

select orders.orderId as 编号,count(*) as 订购产品数  from orderItem
inner join orders on orderItem.orderId = orders.orderId
group by orders.orderId
--6.查询每个订单里的每个类别的产品分别订购了几次和总数量，例如：

-- 订单编号       产品类别     订购次数     总数量

--    1           文具            	2           82
--    1           体育用品        	1            1
--    2           文具            	2           56
--    2           体育用品        	1            2
--    2           日常用品        	1            20

select * from orderItem
inner join orders on orderItem.orderId=orders.orderId
select orders.orderId as 订单编号,itemType as 产品类别,count(*)as 订购次数,sum(theNumber) as 总数量 from orderItem
inner join orders on orderItem.orderId = orders.orderId
group by orders.orderId,itemType
order by orders.orderId